package com.hushan.elevator.dao;

import com.hushan.elevator.model.DeviceMnt;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface DeviceMntRepository extends JpaRepository<DeviceMnt, Integer> {
    // 根据设备ID查询维保记录
    List<DeviceMnt> findByDeviceId(Integer deviceId);


    // 根据创建时间范围查询维保记录
    List<DeviceMnt> findByCreatedTimeBetween(LocalDateTime start, LocalDateTime end);

    // 根据更新时间范围查询维保记录
    List<DeviceMnt> findByUpdatedTimeBetween(LocalDateTime start, LocalDateTime end);

    // 根据是否删除查询维保记录
    List<DeviceMnt> findByIsDeleted(Boolean isDeleted);

    @Query(value = "    select\n" +
            "     dm.mnt_id,\n" +
            "    dm.device_id,\n" +
            "    dm.type,\n" +
            "    dm.mnt_user,\n" +
            "    dm.description,\n" +
            "    dm.start_time,\n" +
            "    dm.end_time,\n" +
            "    dm.suspension_time,\n" +
            "    dm.mnt_status,\n" +
            "    dm.cycle,\n" +
            "    dm.electrical_check,\n" +
            "    dm.electrical_check_remark,\n" +
            "    dm.safety_check,\n" +
            "    dm.safety_check_remark,\n" +
            "    dm.date_time,\n" +
            "    dm.mnt_status_desc,\n" +
            "    dm.created_time,\n" +
            "    dm.updated_time,\n" +
            "    dm.created_by,\n" +
            "    dm.is_deleted,\n" +
            "    devi.serial_number,devi.device_name,\n" +
            "    d.name,d.address,d.parent_id,d.ancestors\n" +
            "            from\n" +
            "    device_info AS devi inner join\n" +
            "    auth_dept as d INNER JOIN\n" +
            "    device_mnt as dm\n" +
            "    ON devi.dept_id = d.dept_id and dm.device_id = devi.device_id\n" +
            "    WHERE d.is_deleted=0 and devi.is_deleted=0 and FIND_IN_SET(:deptId,d.ancestors);", nativeQuery = true)
    List<Object[]> findMntRecordInDept(@Param("deptId") int deptId);
}